<?php defined('SYSPATH') or die('No direct script access.');

class excel {

	private static $columnLettersCache;

	public static function loadClasses() {
		$version = '1.7.9';
		require_once APPPATH . 'vendor/PHPExcel/' . $version . '/Classes/PHPExcel.php';
		require_once APPPATH . 'vendor/PHPExcel/' . $version . '/Classes/PHPExcel/Writer/Excel2007.php';
		require_once APPPATH . 'vendor/PHPExcel/' . $version . '/Classes/PHPExcel/IOFactory.php';
	}

	public static function columnLetters() {
		if (self::$columnLettersCache) {
			return self::$columnLettersCache;
		}
		$columnLetters = array();
		for ($charNumber = ord('A'); $charNumber <= ord('Z'); $charNumber ++) {
			$columnLetters[] = chr($charNumber);
		}
		for ($charNumber = ord('A'); $charNumber <= ord('Z'); $charNumber ++) {
			$columnLetters[] = 'A' . chr($charNumber);
		}
		self::$columnLettersCache = $columnLetters;
		return $columnLetters;
	}

	public static function setMetadata(&$objPHPExcel) {
		$objPHPExcel->getProperties()->setCreator("Sdružení Jóga v denním životě Střílky");
		$objPHPExcel->getProperties()->setLastModifiedBy("Sdružení Jóga v denním životě Střílky");
	}

	public static function writeSheetTable($sheet, $recordList, $firstRowContainsColumnHeaders=FALSE, $keyList=array(), $position=array(1,0)) {
		if (count($keyList) == 0) {
			if (count($recordList) > 0) {
				foreach ($recordList[0] as $key => $value) {
					$keyList[] = $key;
				}
			}
		}
		 
		$columnLetters = self::columnLetters();
		 
		$columnNumber = $position[1];
		foreach ($keyList as $key) {
			$sheet->getColumnDimension($columnLetters[$columnNumber++])->setAutoSize(true);
		}
		 
		/*---------------------------------
		 * Data
		*---------------------------------*/
		 
		$headerStyle = array(
				'borders' => array(
						'outline' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
								'color' => array('argb' => 'FF000000'),
						),
				),
				'font' => array(
						'bold' => true,
				),
				'alignment' => array(
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
						'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
				),
		);
		 
		$rowNumber = $position[0];
		foreach ($recordList as $row) {
			$columnNumber = 0;
			foreach ($keyList as $key) {
				$fieldName = $key;
				$dataType = '';
				if ($key instanceof ColumnDescriptor) {
					$fieldName = $key->fieldName;
					$dataType = $key->dateType;
				}
				$cellCoordinates = $columnLetters[$columnNumber] . $rowNumber;
				$cellValue = $row[$fieldName];
				$sheet->setCellValue($cellCoordinates, $cellValue);
				if ($rowNumber == $position[0] && $firstRowContainsColumnHeaders) {
					$sheet->getStyle($columnLetters[$columnNumber] . $position[0])->applyFromArray($headerStyle);
				}
				else {
					if ($dataType) {
						if ($dataType == ColumnDescriptor::$TYPE_DATE) {
							$year = substr($cellValue,0,4);
							$month = substr($cellValue,5,2);
							$day = substr($cellValue,8,2);
							$dateValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day);
							$sheet->setCellValue($cellCoordinates, $dateValue);
							$sheet->getStyle($cellCoordinates)->getNumberFormat()->setFormatCode('d.m.yyyy');
						}
						else if ($dataType == ColumnDescriptor::$TYPE_TEXT) {
							$sheet->setCellValueExplicit(
									$cellCoordinates,
									$cellValue,
									PHPExcel_Cell_DataType::TYPE_STRING);
						}
					}
				}
				$columnNumber++;
			}
			$rowNumber++;
		}
	}

	public static function simpleReport($recordList, $keyList=array(), $firstRowContainsColumnHeaders = FALSE) {
		self::loadClasses();

		$objPHPExcel = new PHPExcel();

		// Metadata
		self::setMetadata($objPHPExcel);
		$objPHPExcel->setActiveSheetIndex(0);
		$sheet = $objPHPExcel->getActiveSheet();

		self::writeSheetTable($sheet, $recordList, $firstRowContainsColumnHeaders, $keyList);

		// Save the file
		$tmpExcelFileName = tempNam(Kohana::config('yosemin.temp_dir'), 'yoseminXlsx');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save($tmpExcelFileName);

		return $tmpExcelFileName;
	}

	public static function toAssociativeArray($valueList) {
		return json_encode($valueList);
		/*$jsArray = "{";
		 foreach ($valueList as $key => $value) {
		$jsArray .= "'" . $key . "':'" . self::escape($value) . "',";
		}
		if (strlen($jsArray) > 1) {
		$jsArray = substr($jsArray, 0, -1);
		}
		$jsArray .= "}";
		return $jsArray;
		*/
	}

	private static function escape($text) {
		$text = str_replace("\\", "\\\\", $text);
		$text = str_replace("'", "\\'", $text);
		return $text;
	}

	public static function writeVillageRecord($sheet, $viRegistration, $rowNumber, $columnLetters, $dataCellStyle, $nameStyle, $lastColumn, $writeReason=false) {
		$columnNumber = 0;

		$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, format::personFullName($viRegistration, TRUE));

		// $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, format::date($viRegistration['birth_date']));
		$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, format::address($viRegistration));
		$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['idcard_number']);
		$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['night_count']);

		$recreationFee = bcmul(Kohana::config('yosemin.village-recreationFee'), $viRegistration['night_count'], sql::$DECIMAL_PRECISION);
		$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $recreationFee);

		// If the person is accomodated on the floor, we do not pay for accomodating him
		$accomodationFee = 0;
		if ($viRegistration['vi_accomodation_type_code'] != 'hala_spacak') {
			$accomodationFee = bcmul(Kohana::config('yosemin.village-accomodationFee'), $viRegistration['night_count'], sql::$DECIMAL_PRECISION);
		}
		$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $accomodationFee);

		if ($writeReason) {
			$reason = '???';
			if ($viRegistration['is_ztp']) {
				$reason = 'ZTP';
			}
			else if ($viRegistration['night_count'] == 0) {
				$reason = 'Nula nocí';
			}
			else if ($viRegistration['vi_accomodation_type_code'] == 'bez_ubytovani') {
				$reason = 'Bez ubytování';
			}
			else if (date::personAge($viRegistration['birth_date']) < 18) {
				$reason = 'Mladší 18 let';
			}
			else if (date::personAge($viRegistration['birth_date']) > 70) {
				$reason = 'Starší 70 let';
			}
			else if (! $viRegistration['birth_date']) {
				$reason = 'Bez data narození';
			}
			$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $reason);
		}

		foreach ($columnLetters as $column) {
			if ($column == $lastColumn) {
				break;
			}
			$sheet->getStyle($column . $rowNumber)->applyFromArray($dataCellStyle);
		}
		$sheet->getStyle('A' . $rowNumber)->applyFromArray($nameStyle);
		$sheet->getStyle('B' . $rowNumber)->applyFromArray($nameStyle);
	}
}

